import threading
import time
import logging
import ctypes
import inspect
import gc

import requests
from concurrent.futures import ProcessPoolExecutor,ThreadPoolExecutor, wait, ALL_COMPLETED ,as_completed
from operator import itemgetter
from itertools import groupby
from database_operation import operate_record
from utils import position_util
from urllib import request, parse
from utils import array_util


'''
* 重庆POI数据
* 根据矩形查询poi
* 050000,060000,100000,120000  单爬
* 070000|080000|090000|140000|150000|160100|160400|170000 一起爬
* @param polygon 高德坐标系6位 左上|右下
* @param page 1
* @param list []
* @param orgin [pane_id, orgin_polygon]
'''
logger = logging.getLogger()  # 不加名称设置root logger
logger.setLevel(logging.DEBUG)
formatter = logging.Formatter(
    '%(asctime)s - %(name)s - %(threadName)s - %(levelname)s: - %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S')
# 使用FileHandler输出到文件
fh = logging.FileHandler('log.txt')
fh.setLevel(logging.INFO)
fh.setFormatter(formatter)
# 使用StreamHandler输出到屏幕
ch = logging.StreamHandler()
ch.setLevel(logging.INFO)
ch.setFormatter(formatter)
# 添加两个Handler
logger.addHandler(ch)
logger.addHandler(fh)


def get_poi_by_polygon_origin(page, polygon, list, orgin):
    url = "https://lbs.amap.com/dev/api?polygon={0}&keywords=&types=070000|080000|090000|140000|150000|160100|160400|170000&offset=50&page={1}&extensions=all"
    url = url.format(polygon, page)
    logger.info(url)
    payload = {'type': 'place/polygon', 'version': 'v3'}
    headers = {'content-type': 'application/x-www-form-urlencoded', 'x-requested-with': 'XMLHttpRequest'}
    data = parse.urlencode(payload).encode(encoding='utf8')
    req = request.Request(url, data=data, headers=headers)
    # 等一会2s
    time.sleep(2)
    try:
        with request.urlopen(req, timeout=10) as res:
            # logger.info('Status:'+str(res.status)+','+str(res.reason))
            res_str = str(res.read(), encoding='utf8')
            logger.info('----->'+res_str)
            res_json = eval(res_str)
    except request.HTTPError:
        logger.info('----->HTTPError:')
        return None
    except request.URLError:
        logger.error('----->URLError:')
        return None
    except NameError:
        logger.error('----->NameError:')
        return None
    except Exception:
        logger.error('----->Exception:')
        return None
    status = res_json["status"]  # 1 成功
    is_success = False
    if status != '1':
        print('高德接口获取失败,' + str(res_json['info']))
        return [is_success, list, orgin]
    else:
        is_success = True
    count = int(res_json["count"])
    if count == 0:
        return [is_success, list, orgin]

    pois = res_json["pois"]
    if pois is not None and len(pois) > 0:
        list.extend(pois)
    else:
        return [is_success, list, orgin]
    page_num = int(count / 50) if count % 50 == 0 else int(count / 50) + 1
    if page >= page_num:
        return [is_success, list, orgin]
    page = page + 1
    return get_poi_by_polygon_origin(page, polygon, list, orgin)


def get_city_pane(city='重庆市'):
    pane_sql = "select id,st_xmin(geom) as left_x,st_ymax(geom) as left_y,st_xmax(geom) as right_x,st_ymin(geom) as right_y,st_astext(geom) as orgin_polygon from area_pane where city='{0}' and name!='原型' and istouch=true and is_success=false "
    pane_sql = pane_sql.format(city)
    rows = operate_record.query_record_list(pane_sql)
    if len(rows) == 0:
        print('-------暂无数据')
        return None
    # group_list = array_util.list_split_by_size(len(rows), 14)
    group_list = array_util.list_split_by_size(len(rows), 40)
    if not group_list:
        print('-------暂无数据')
        return
    print(group_list)
    for s in group_list:
        sts = s.split(",", 2)
        thread_list = rows[int(sts[0]):int(sts[1]) + 1]
        all_task = []
        executor = ThreadPoolExecutor(max_workers=len(thread_list), thread_name_prefix='ThreadPool')
        for orgin in thread_list:
            left_x = orgin[1]
            left_y = orgin[2]
            right_x = orgin[3]
            right_y = orgin[4]
            left = position_util.gps84_to_gcj02(left_x, left_y)
            right = position_util.gps84_to_gcj02(right_x, right_y)
            polygon = str(round(left[0], 6)) + ',' + str(round(left[1], 6)) + '|' + \
                      str(round(right[0], 6)) + ',' + str(round(right[1], 6))
            # 多线程处理
            task = executor.submit(get_poi_by_polygon_origin, 1, polygon, [], (orgin[0], orgin[5]))
            all_task.append(task)
        # 等待全部执行完成
        count_list = []
        for task in as_completed(all_task):
            result = task.result()
            if not result:
                continue
            is_success = result[0]
            poi_list = result[1]
            pane_id = result[2][0]
            orgin_polygon = result[2][1]
            count_list.append([pane_id, is_success, orgin_polygon, poi_list])
        executor.shutdown(wait=True)
        # 批量新增
        batch_insert(count_list)

    print('------>{ finished }')


def batch_insert(data_list):
    # 成功的ID列表
    success_id_values = []
    poi_values = []
    data_list_size = len(data_list)
    for k in range(0, data_list_size):
        data = data_list[k]
        is_success = data[1]
        if is_success is False:
            continue
        pane_id = data[0]
        success_id_values.append("(" + str(pane_id) + "," + str(is_success) + ")")
        orgin_polygon = data[2]
        geom = "ST_GeomFromText('" + orgin_polygon + "', 4326)"
        poi_list = data[3]
        poi_size = len(poi_list)
        for i in range(0, poi_size):
            poi = poi_list[i]
            poi_id = poi['id']
            name = str(poi['name'])
            address = str(poi['address'])
            name = name.replace("'", '')
            if address is not None:
                address = address.replace("'", '')
            type = poi['type']
            typecode = poi['typecode']
            tel = poi['tel']
            pname = poi['pname']
            cityname = poi['cityname']
            adname = poi['adname']
            location = poi['location']

            poi_value = "('" + name + "','" + type + "','" + typecode + "','" + str(tel) + "','" + poi_id + "','" \
                                   + pname + "','" + cityname + "','" + adname + "','" + address + "','" + location + "'," + geom + ')'
            poi_values.append(poi_value)

    if len(poi_values) > 0:
        batch_insert_values = ''
        for i in range(0, len(poi_values)):
            poi_value = poi_values[i]
            batch_insert_values += poi_value
            if i < len(poi_values)-1:
                batch_insert_values += ','
        batch_insert_sql = 'insert into chongqing_poi(name,type,typecode,tel,poi_id,pname,cityname,adname,address,location,geom) values '
        batch_insert_sql += batch_insert_values
        operate_record.save_record(batch_insert_sql)

    if len(success_id_values) > 0:
        id_values=''
        for i in range(0, len(success_id_values)):
            ids = success_id_values[i]
            id_values += ids
            if i < len(success_id_values)-1:
                id_values += ','
        batch_update_sql = "update area_pane set is_success=tmp.is_success from (values {0}) as tmp (id,is_success) where area_pane.id=tmp.id"
        batch_update_sql = batch_update_sql.format(id_values)
        print(batch_update_sql)
        operate_record.save_record(batch_update_sql)


# 根据POI去重
def batch_poi_duplicate():
    sql = "select t.poi_id from chongqing_poi t group by t.poi_id having count(t.poi_id)>1"
    rows = operate_record.query_record_list(sql)
    if len(rows) == 0:
        print('-------暂无数据')
        return None
    group_list = array_util.list_split_by_size(len(rows), 500)
    if not group_list:
        print('-------暂无数据')
        return None
    for s in group_list:
        sts = s.split(",", 2)
        thread_list = rows[int(sts[0]):int(sts[1]) + 1]
        poi_id_values = ''
        for i in range(len(thread_list)):
            poi_tuple = thread_list[i]
            poi_id_values += "'"+poi_tuple[0]+"'"
            if i < len(thread_list)-1:
                poi_id_values += ','
        poi_id_sql = 'select t.id,t.poi_id from chongqing_poi t where t.poi_id in ({0})'
        poi_id_sql = poi_id_sql.format(poi_id_values)
        duplicate_rows = operate_record.query_record_list(poi_id_sql)
        duplicate_rows_list = []
        for dup in duplicate_rows:
            duplicate_rows_list.append({'id': dup[0], 'poi_id': dup[1]})
        duplicate_rows_list.sort(key=itemgetter('poi_id'))
        del_ids = []
        for poi_id, items in groupby(duplicate_rows_list, key=itemgetter('poi_id')):
            # group by 返回 一个值 和 一个迭代器对象
            # 跳过第一个
            next(items)
            for i in items:
                del_ids.append(i['id'])
        del_id_values = ''
        for i in range(len(del_ids)):
            del_id_values += str(del_ids[i])
            if i < len(del_ids)-1:
                del_id_values += ','
        del_sql = 'delete from chongqing_poi where id in({0})'
        del_sql = del_sql.format(del_id_values)
        print(del_sql)
        operate_record.save_record(del_sql)
    print('--------->finished')


def test():
    url ='https://lbs.amap.com/dev/api?polygon=116.88673,31.666035|116.891973,31.661521&keywords=&types=050000&offset=50&page=1&extensions=all'
    logger.info(url)
    payload = {'type': 'place/polygon', 'version': 'v3'}
    headers = {'content-type': 'application/x-www-form-urlencoded', 'x-requested-with': 'XMLHttpRequest'}
    # req = request.Request(url, headers=headers, data=payload)
    data = parse.urlencode(payload).encode(encoding='utf8')
    req = request.Request(url, data=data, headers=headers)
    response = request.urlopen(req)
    res_str = str(response.read(), encoding='utf8')
    res_json = eval(res_str)
    logger.info(res_json)
    logger.info(res_json['info'])


def main():
    # 1.根据城市网格获取城市POI数据
    get_city_pane()
    # 2.根据POI_ID去重
    # batch_poi_duplicate()


if __name__ == '__main__':
    main()